package com.es.pay.visitorScore;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class VisitorScoreBiz {

	public boolean importVisitorGradeExcelList(
			ArrayList<ArrayList> queryParms) throws Exception {
		Dao dao = new Dao();
		try {
			String sql = "insert into hrm_mystery_visitor_grade_tmp(v_date,v_oversee,shop_id,v_shop_name,v_area,v_province,v_city,v_city_level,v_dealer_name,v_shop_address,v_position_code,v_acershop_level,v_type,v_tufa,v_sum_grade,session_id,row_id,create_time) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
			dao.updateBatch(sql, queryParms);
		} catch (Exception e) {
			System.out.print(e.getMessage());
			return false;
		} finally {
			dao.close();
		}
		return true;
	}
	
	@SuppressWarnings("unchecked")
	public ArrayList<HashMap<String, Object>> importVisitorGradeDo(String sessionId,String intime) throws Exception{
		Dao dao = new Dao();
		ArrayList<HashMap<String, Object>> map = new ArrayList<HashMap<String, Object>>();
		ArrayList<String> query = new ArrayList<String>();
		query.add(sessionId);
		query.add(intime);
		try {
			String sql = ("{call visitor_grade(?,?)}");
			ResultSet resultSet = dao.executePro(sql,query);
			ResultSetMetaData rss = resultSet.getMetaData();
			
			while(resultSet.next()){
				if(rss.getColumnCount()==1){
					HashMap<String, Object> rowNum = new HashMap<String, Object>();
					rowNum.put("rowNum", (String) resultSet.getObject(rss.getColumnName(1)));
					map.add(rowNum);
					return map;
				}
				HashMap<String, Object> errorlist = new HashMap<String, Object>();
				// row_id,error
				for(int i = 0;i<rss.getColumnCount(); i++){ 	
					errorlist.put(rss.getColumnName(i+1), (String) resultSet.getObject(rss.getColumnName(i+1)));
			 }	
				map.add(errorlist);
		  }
		} catch(Exception e){
				System.out.print(e.getMessage());
		   }finally {		   
			   dao.close();
		    }
		    return map;
	}
	
	public PageBean queryVisitorGradeList(PageNavigator pageNav, ArrayList<String> param) throws Exception{
		Dao dao = new Dao();
		PageBean pageBean = new  PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select *  from hrm_mystery_visitor_grade rate"
						+" where '1'='1' ";
			String[] sqlArrary	= new String[] {"and  rate.v_date=? ",
						"and UPPER(rate.v_oversee) like '%'+UPPER(?)+'%' ",
						"and UPPER(rate.v_shop_name) like '%'+UPPER(?)+'%' ",
						"and UPPER(rate.shop_id) like '%'+UPPER(?)+'%' ",
						"and rate.v_area = (select top 1 area_value from hrm_area where area_code=?) ",
						"and rate.v_province = (select top 1 province_name from hrm_province where province_code=?) ",
						"and rate.v_city = (select top 1 city_name from hrm_city where city_code=?) "};
			for (int i = 0; i < param.size(); ++i) {
			      if(!param.get(i).equals("")){
			    	  sql += sqlArrary[i];
			    	  query.add(param.get(i).trim());
			      }
			  }
			int total = dao.getAllRow(sql,query);		
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total%pageNav.getPageRow()==0?total/pageNav.getPageRow():total/pageNav.getPageRow()+1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally{
			dao.close();
		}
		return pageBean;
	}
	
	public ArrayList<HashMap<String,String>>  exceptExcelVisitorGradeList(ArrayList<String> param) throws Exception{
		Dao dao = new Dao();
		ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select *  from hrm_mystery_visitor_grade rate where '1'='1' ";
				String[] sqlArrary	= new String[] {"and  rate.v_date=? ",
				"and UPPER(rate.v_oversee) like '%'+UPPER(?)+'%' ",
				"and UPPER(rate.v_shop_name) like '%'+UPPER(?)+'%' ",
				"and UPPER(rate.shop_id) like '%'+UPPER(?)+'%' ",
				"and rate.v_area = (select top 1 area_value from hrm_area where area_code=?) ",
				"and rate.v_province = (select top 1 province_name from hrm_province where province_code=?) ",
				"and rate.v_city = (select top 1 city_name from hrm_city where city_code=?) "};
			for (int i = 0; i < param.size(); ++i) {
			      if(!param.get(i).equals("")){
			    	  sql += sqlArrary[i];
			    	  query.add(param.get(i).trim());
			      }
			  }
			ResultSet resultSet = null;
			if(query.size()==0)
				resultSet = dao.Select(sql);
			else
				resultSet = dao.Select(sql,query);
			ResultSetMetaData rss = resultSet.getMetaData();
			
			while(resultSet.next()){
					HashMap<String,String> map = new HashMap<String,String>();
					for(int i = 0;i<rss.getColumnCount(); i++){ 	
						if(!"vgid".equalsIgnoreCase(rss.getColumnName(i+1)))
							map.put(rss.getColumnName(i+1), (String) resultSet.getObject(rss.getColumnName(i+1)));							
				 }	
				  list.add(map);
			  }
		} finally{
			dao.close();
		}
		return list;
	}
	
	public ArrayList<HashMap<String,String>> getYYYYList() throws Exception{
		Dao dao = new Dao();
		ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
		try {
			String sql = "select distinct v_date code,v_date value  from hrm_mystery_visitor_grade ";
			ResultSet resultSet = dao.Select(sql);
			while(resultSet.next()){
					HashMap<String,String> map = new HashMap<String,String>();
					map.put("code", resultSet.getString("code"));
					map.put("value", resultSet.getString("value"));
				  list.add(map);
			  }
		} finally{
			dao.close();
		}
		return list;
	}
	
	/**
	 * 删除
	 * @param emp_id
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public boolean deleteVisitorGradeDo(String targetRateId ) throws Exception{
		if(targetRateId==null) return false;
		Dao dao = new Dao();
		ArrayList query = new ArrayList();
		query.add(targetRateId);
		try {
			String sql = "delete from  hrm_mystery_visitor_grade  where vgid=?";
			dao.update(sql,query);
		} catch(Exception e){
				System.out.print(e.getMessage());
			   return false;
		   }finally {		   
			   dao.close();
		    }
		    return true;
	}
	
	/**
	 * 删除
	 * @param emp_id
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public boolean deleteAllVisitorGradeDo(ArrayList<ArrayList> queryParms) throws Exception{
		Dao dao = new Dao();
		try {
			String sql = "delete from  hrm_mystery_visitor_grade  where vgid=?";
			dao.updateBatch(sql, queryParms);
		} catch(Exception e){
				System.out.print(e.getMessage());
			   return false;
		   }finally {		   
			   dao.close();
		    }
		    return true;
	}
}
